/*
 * Copyright © 2004-2014 chenYuan. All rights reserved.
 * @Website:wwww.jspx.net
 * @Mail:39793751@qq.com
 * @author: chenYuan , 陈原
 * @License: Jspx.net Framework Code is open source (LGPL)，Jspx.net Framework 使用LGPL 开源授权协议发布。
 * @jvm:jdk1.6+  x86/amd64
 *
 */
package com.jspx.sober.dialect;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import com.jspx.sober.TableModels;
import com.jspx.utils.DateUtil;
import com.jspx.utils.NumberUtil;
import com.jspx.utils.ObjectUtil;
import com.jspx.utils.StringUtil;

/**
 * Created by IntelliJ IDEA.
 * User:chenYuan (mail:39793751@qq.com)
 * Date: 2007-1-6
 * Time: 22:08:11
 * MySQL数据库 SQL匹配
 */
public class MySQLDialect extends Dialect
{

    public MySQLDialect()
    {
        //ENGINE=MyISAM
        standard_SQL.put(SQL_CREATE_TABLE, "CREATE TABLE `${" + KEY_TABLE_NAME + "}` \n(\n" +
                " <#list column=" + KEY_COLUMN_LIST + ">${column},\n</#list>" +
                " \nPRIMARY KEY  (`${" + KEY_PRIMARY_KEY + "}`)\n) DEFAULT CHARSET=UTF8;");

        standard_SQL.put(SQL_INSERT, "INSERT INTO ${" + KEY_TABLE_NAME + "} (<#list field=" + KEY_FIELD_LIST + ">`${field}`<#if where=field_has_next>,</#if></#list>) VALUES (<#list x=1.." + KEY_FIELD_COUNT + ">?<#if x_has_next>,</#if></#list>)");
        standard_SQL.put(SQL_DELETE, "DELETE FROM ${" + KEY_TABLE_NAME + "} WHERE `${" + KEY_FIELD_NAME + "}`=<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>${" + KEY_FIELD_VALUE + "}<#ifwhere= " + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>");
        standard_SQL.put(SQL_DELETE_IN, "DELETE FROM ${" + KEY_TABLE_NAME + "} WHERE `${" + KEY_FIELD_NAME + "}` IN (<#list fvalue=" + KEY_FIELD_VALUE + ">'${fvalue}'<#if where=fvalue_has_next>,</#if></#list>)");
        standard_SQL.put(SQL_UPDATE, "UPDATE ${" + KEY_TABLE_NAME + "} SET <#list field=" + KEY_FIELD_LIST + ">`${field}`=?<#if where=field_has_next>,</#if></#list> WHERE ${" + KEY_FIELD_NAME + "}=<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>${" + KEY_FIELD_VALUE + "}<#if where=" + KEY_FIELD_NAME + FIELD_QUOTE + ">'</#if>");
        
        standard_SQL.put(String.class.getName(), "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;255>text<#else>varchar(${" + COLUMN_LENGTH + "})</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default '${" + COLUMN_DEFAULT + "}'</#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(Integer.class.getName(), "`${" + COLUMN_NAME + "}` integer <#if where=" + KEY_FIELD_SERIAL + ">AUTO_INCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put("int", "`${" + COLUMN_NAME + "}` integer <#if where=" + KEY_FIELD_SERIAL + ">AUTO_INCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(Long.class.getName(), "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;16>bigint(${" + COLUMN_LENGTH + "})<#else>bigint(16)</#else></#if> <#if where=" + KEY_FIELD_SERIAL + ">AUTO_INCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else>  default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put("long", "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;16>bigint(${" + COLUMN_LENGTH + "})<#else>bigint(16)</#else></#if> <#if where=" + KEY_FIELD_SERIAL + ">AUTO_INCREMENT</#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL<#else>  default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if></#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(Double.class.getName(), "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;15>decimal(${" + COLUMN_LENGTH + "},3)<#else>decimal(15,3)</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put("double", "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;15>decimal(${" + COLUMN_LENGTH + "},3)<#else>decimal(15,3)</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(Float.class.getName(), "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;9>decimal(${" + COLUMN_LENGTH + "},2)<#else>decimal(10,2)</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put("float", "`${" + COLUMN_NAME + "}` <#if where=" + COLUMN_LENGTH + "&gt;9>decimal(${" + COLUMN_LENGTH + "},2)<#else>decimal(9,2)</#else></#if> <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default <#if where=!" + COLUMN_DEFAULT + ">'0'<#else>'${" + COLUMN_DEFAULT + "}'</#else></#if> COMMENT '${" + COLUMN_CAPTION + "}'");

        standard_SQL.put(Boolean.class.getName(), "`${" + COLUMN_NAME + "}` int(1) <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default ${" + COLUMN_DEFAULT + ".toInt()}</#if>  COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(boolean.class.getName(), "`${" + COLUMN_NAME + "}` int(1) <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default ${" + COLUMN_DEFAULT + ".toInt()}</#if>  COMMENT '${" + COLUMN_CAPTION + "}'");

        standard_SQL.put(Date.class.getName(), "`${" + COLUMN_NAME + "}` datetime <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> default '0000-00-00 00:00:00' COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(byte[].class.getName(), "`${" + COLUMN_NAME + "}` LONGBLOB COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(InputStream.class.getName(), "`${" + COLUMN_NAME + "}` LONGBLOB COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(char.class.getName(), "`${" + COLUMN_NAME + "}` char(2) <#if where=" + COLUMN_NOT_NULL + ">NOT NULL</#if> <#if where=" + COLUMN_DEFAULT + ">default '${" + COLUMN_DEFAULT + "}'</#if> COMMENT '${" + COLUMN_CAPTION + "}'");
        standard_SQL.put(SQL_DROP_TABLE, "DROP TABLE IF EXISTS ${" + KEY_TABLE_NAME + "}");
        standard_SQL.put(FUN_TABLE_EXISTS, "show tables like '${" + KEY_TABLE_NAME + "}'");


        //修改系列开始
        //ALTER SEQUENCE ${" + SERIAL_NAME + "} RESTART WITH ${" + KEY_SEQUENCE_RESTART + "}
        standard_SQL.put(ALTER_SEQUENCE_RESTART, "Alter Table ${" + KEY_TABLE_NAME + "} Auto_increment=${" + KEY_SEQUENCE_RESTART + "}");

        standard_SQL.put(SQL_TABLE_NAMES, "SHOW TABLES");

        standard_SQL.put(DATABASE_SIZE, "SELECT SUM(data_length) + SUM(index_length) as size FROM information_schema.tables where table_schema = '${" + KEY_TABLE_NAME + "}'");

        standard_SQL.put(SEQUENCE_NAME, "SHOW TABLE STATUS LIKE '${" + KEY_TABLE_NAME + "}'");

         //查询表状态信息, 包括自增量
        //SHOW TABLE STATUS LIKE 'jq_question'
    }

    public String getLimitString(String sql, int begin, int end, TableModels soberTable)
    {
        int length = end - begin;
        if (length<0) length = 0;
        return sql + " limit " + begin + "," + length;
    }

    public void setPreparedStatementValue(PreparedStatement pstmt, int parameterIndex, Object obj) throws Exception {
        //支持boolean 类型
        if (obj instanceof Boolean) {
            pstmt.setInt(parameterIndex, ObjectUtil.toInt(ObjectUtil.toBoolean(obj)));
            return;
        }
        super.setPreparedStatementValue(pstmt, parameterIndex, obj);
    }

    public boolean supportsSequenceName()
    {
        return false;
    }

    public boolean supportsLimit()
    {
        return true;
    }

    public boolean supportsSavePoints()
    {
        return false;
    }

    public boolean supportsGetGeneratedKeys()
    {
        return true;
    }

    public boolean commentPatch()
    {
        return false;
    }


    /**
     * @param rs    数据
     * @param index 索引
     * @return 返回查询结果
     * @throws java.sql.SQLException 异常
     */
    public Object getResultSetValue(ResultSet rs, int index) throws SQLException {
        if (rs==null) return null;
        String typeName = null;
        int colSize = 0;
        try {
            typeName = rs.getMetaData().getColumnTypeName(index).toLowerCase();
            colSize = rs.getMetaData().getColumnDisplaySize(index);


            if (typeName.equalsIgnoreCase("tinyint"))
            {
                return rs.getBoolean(index);
            }
            //短断整型
            if ((typeName.equals("int") && colSize < 4) || typeName.equals("short") || typeName.equals("smallint") || typeName.equals("int2") || (typeName.equalsIgnoreCase("fixed") && colSize < 4)) {
                return rs.getShort(index);
            }

            ///////长整型
            if (typeName.equals("bigserial") || typeName.equals("long") || typeName.equals("bigint") || typeName.equals("int8") || (typeName.equals("fixed") && colSize > 18)) {
                return rs.getLong(index);
            }

            //////////整型
            if ( typeName.equals("integer") || typeName.equals("serial") || typeName.contains("int") || (typeName.equals("number") && colSize < 8) || (typeName.equals("fixed") && colSize < 19)) {
                return rs.getInt(index);
            }

            ///////单精度
            if (typeName.equals("money") || typeName.equals("float") || typeName.equals("real") || typeName.equals("binary_float")) {
                return rs.getFloat(index);
            }
            ///////大数值
            if (typeName.contains("decimal")) {
                //mysql float 精度有问题,默认使用 decimal
                return rs.getBigDecimal(index);
            }
            ///////双精度
            if (typeName.equals("double") || typeName.equals("double precision") || typeName.equals("binary_double")) {
                return rs.getDouble(index);
            }

            ///////日期
            if (typeName.equals("date")) {
                java.sql.Date t = rs.getDate(index);
                if (t == null) return DateUtil.empty;
                return new java.util.Date(t.getTime());
            }

            ///////日期时间java.sql.Timestamp
            if (typeName.contains("timestamp") || typeName.equals("datetime")) {
                Timestamp t = rs.getTimestamp(index);
                if (t == null) return DateUtil.empty;
                return new java.util.Date(t.getTime());
            }

            ////////////时间
            if (typeName.equalsIgnoreCase("time")) {
                Time t = rs.getTime(index);
                if (t == null) return DateUtil.empty;
                return t;
            }

            ///////短字符串
            if (typeName.equals("char") || typeName.equals("text")|| typeName.equals("nvarchar") || typeName.equals("varchar") || typeName.equals("varchar2")) {
                return rs.getString(index);
            }

            ///////二进制类型 文件类型
            if (typeName.equalsIgnoreCase("bytea") || typeName.equalsIgnoreCase("image") || typeName.contains("blob")  || typeName.equalsIgnoreCase("image") || typeName.equalsIgnoreCase("long byte")
                    || typeName.equalsIgnoreCase("varbinary") || typeName.equalsIgnoreCase("binary") || typeName.equalsIgnoreCase("long byte")) {

                Blob blob = rs.getBlob(index);
                if (blob==null) return StringUtil.empty;
                return blob.getBinaryStream();
            }
            return rs.getObject(index);
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("typeName=" + typeName + " size=" + colSize + " columnName=" + rs.getMetaData().getColumnName(index), e);
        }
        return null;
    }

}